﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Microsoft.ApplicationBlocks.Data;
using GotDotNet.ApplicationBlocks.Data;

namespace LibraryUtility.Common
{
    public  class CommonClass
    {
        private static readonly string ConnString = ConnConfig.ConnString;
        #region 成员方法
        /// <summary>
        /// Gets the list.
        /// </summary>
        /// <param name="strTable">The STR table.</param>
        /// <param name="fldStr">The FLD STR.</param>
        /// <param name="strWhere">The STR where.</param>
        /// <param name="PageIndex">Index of the page.</param>
        /// <param name="PageSize">Size of the page.</param>
        /// <param name="strSort">The STR sort.</param>
        /// <param name="isGetCount">The is get count.</param>
        /// <returns></returns>
        public static DataSet GetList(string strTable, string fldStr, string strWhere, int PageIndex, int PageSize, string strSort, int isGetCount)
        {
            SqlParameter[] parameters = {
                                            new SqlParameter("@tab", SqlDbType.VarChar, 1000),
                                            new SqlParameter("@strFld", SqlDbType.VarChar, 1500),
                                            new SqlParameter("@strWhere",SqlDbType.VarChar,4000),
                                            new SqlParameter("@Page", SqlDbType.Int),
                                            new SqlParameter("@RecsPerPage", SqlDbType.Int),
                                            new SqlParameter("@Sort", SqlDbType.VarChar,255),
                                            new SqlParameter("@isGetCount", SqlDbType.Bit),
            };

            parameters[0].Value = strTable;  //表名
            parameters[1].Value = fldStr;
            parameters[2].Value = strWhere;
            parameters[3].Value = PageIndex;
            parameters[4].Value = PageSize;
            parameters[5].Value = strSort;
            parameters[6].Value = isGetCount;
            return SqlHelper.ExecuteDataset(ConnString, "Sp_Paging", parameters);
        }
        /// <summary>
        /// Gets the list.
        /// </summary>
        /// <param name="new_connstring">The new_connstring.</param>
        /// <param name="strTable">The STR table.</param>
        /// <param name="fldStr">The FLD STR.</param>
        /// <param name="strWhere">The STR where.</param>
        /// <param name="PageIndex">Index of the page.</param>
        /// <param name="PageSize">Size of the page.</param>
        /// <param name="strSort">The STR sort.</param>
        /// <param name="isGetCount">The is get count.</param>
        /// <returns></returns>
        public static DataSet GetList(string new_connstring, string strTable, string fldStr, string strWhere, int PageIndex, int PageSize, string strSort, int isGetCount)
        {
            SqlParameter[] parameters = {
                                            new SqlParameter("@tab", SqlDbType.VarChar, 1000),
                                            new SqlParameter("@strFld", SqlDbType.VarChar, 1500),
                                            new SqlParameter("@strWhere",SqlDbType.VarChar,4000),
                                            new SqlParameter("@Page", SqlDbType.Int),
                                            new SqlParameter("@RecsPerPage", SqlDbType.Int),
                                            new SqlParameter("@Sort", SqlDbType.VarChar,255),
                                            new SqlParameter("@isGetCount", SqlDbType.Bit),
            };

            parameters[0].Value = strTable;  //表名
            parameters[1].Value = fldStr;
            parameters[2].Value = strWhere;
            parameters[3].Value = PageIndex;
            parameters[4].Value = PageSize;
            parameters[5].Value = strSort;
            parameters[6].Value = isGetCount;
            return SqlHelper.ExecuteDataset(new_connstring, "Sp_Paging", parameters);
        }

        /// <summary>
        /// Gets the list.
        /// </summary>
        /// <param name="strSql">The STR SQL.</param>
        /// <returns></returns>
        public static DataSet GetList(string strSql)
        {
            return SqlHelper.ExecuteDataset(ConnString, CommandType.Text, strSql);
        }

        /// <summary>
        /// Gets the list.
        /// </summary>
        /// <param name="new_connstring">The new_connstring.</param>
        /// <param name="strSql">The STR SQL.</param>
        /// <returns></returns>
        public static DataSet GetList(string new_connstring, string strSql)
        {
            return SqlHelper.ExecuteDataset(new_connstring, CommandType.Text, strSql);
        }

        /// <summary>
        /// Execs the scalar.
        /// </summary>
        /// <param name="new_connstring">The new_connstring.</param>
        /// <param name="sql">The SQL.</param>
        /// <returns></returns>
        public static string ExecScalar(string new_connstring, string sql)
        {
            object result = SqlHelper.ExecuteScalar(new_connstring, CommandType.Text, sql);
            if (result == DBNull.Value || result == null)
            {
                return "";
            }
            else
            {
                return result.ToString();
            }
        }

        /// <summary>
        /// Execs the scalar.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns></returns>
        public static string ExecScalar(string sql)
        {
            object result = SqlHelper.ExecuteScalar(ConnString, CommandType.Text, sql);
            if (result == DBNull.Value || result == null)
            {
                return "";
            }
            else
            {
                return result.ToString();
            }

        }

        /// <summary>
        /// Execs the non query.
        /// </summary>
        /// <param name="new_connstring">The new_connstring.</param>
        /// <param name="sql">The SQL.</param>
        /// <returns></returns>
        public static int ExecNonQuery(string new_connstring, string sql)
        {
            return SqlHelper.ExecuteNonQuery(new_connstring, CommandType.Text, sql);
        }

        /// <summary>
        /// Execs the non query.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns></returns>
        public static int ExecNonQuery(string sql)
        {
            return SqlHelper.ExecuteNonQuery(ConnString, CommandType.Text, sql);
        }
        /// <summary>
        /// Executes the SQLS.
        /// </summary>
        /// <param name="commandTextS">The command text S.</param>
        /// <returns></returns>
        public static bool ExecuteSQLS(string[] commandTextS)
        {
            bool returnValue = true;
            SqlConnection myConnection = new SqlConnection(ConnString);
            myConnection.Open();
            SqlTransaction myTrans = myConnection.BeginTransaction();
            SqlCommand myCommand = myConnection.CreateCommand();
            myCommand.Connection = myConnection;
            myCommand.Transaction = myTrans;
            try
            {
                for (int i = 0; i < commandTextS.Length; i++)
                {
                    myCommand.CommandText = commandTextS[i].ToString();
                    myCommand.ExecuteNonQuery();
                }
                myTrans.Commit();
            }
            catch
            {
                myTrans.Rollback();
                returnValue = false;
            }
            myTrans.Dispose();
            myCommand.Dispose();
            myCommand.Connection.Close();
            return returnValue;
        }

        #endregion
    }
}
